﻿using System;
using System.Collections.Generic;
using System.Text;
using WebApiMIT3.Database;
using WebApiMIT3.Models;

namespace WebApiMIT3.DatabaseAccessLayer
{
    public class PoliticalInfo
    {
        private const string SourceName = "SourceName";
        private const string SourceURL = "SourceURL";
        private const string Title = "Title";
        private const string Author = "Author";
        private const string SummaryDescription = "SummaryDescription";
        private const string FullDescription = "FullDescription";
        private const string OriginalPublishDate = "OriginalPublishDate";
        private const string UpdatePublishDate = "UpdatePublishDate";
        private const string Enclosure = "Enclosure";
        private const string CrawlTimeStamp = "CrawlTimeStamp";
        private const string Removed = "Removed";
        private const string DateFormat = "yyyy-MM-dd HH:mm:ss";

        private readonly string infoID = "InfoID";
        private readonly string tableName = "PoliticalInfo";

        public string InfoID
        {
            get { return this.infoID; }
        }

        public string TableName
        {
            get { return this.tableName; }
        }

        public void InsertStatement(DataModel dataModel)
        {
            var builder = new StringBuilder();

            builder.Append(string.Format("INSERT INTO {0} ", this.tableName));
            builder.Append(
                string.Format(
                "({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}) ",
                SourceName,
                SourceURL,
                Title,
                Author,
                SummaryDescription,
                FullDescription,
                OriginalPublishDate,
                UpdatePublishDate,
                Enclosure,
                CrawlTimeStamp,
                Removed));
            builder.Append(
                string.Format(
                "VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', {9}, {10});",
                this.ReplaceDBChars(dataModel.SourceName),
                this.ReplaceDBChars(dataModel.SourceUrl),
                this.ReplaceDBChars(dataModel.Title),
                this.ReplaceDBChars(dataModel.Author),
                this.ReplaceDBChars(dataModel.SummaryDescription),
                this.ReplaceDBChars(dataModel.FullDescription),
                this.ReplaceDBChars(dataModel.OriginalPublishDate),
                this.ReplaceDBChars(dataModel.UpdatePublishDate),
                this.ReplaceDBChars(dataModel.Enclosure),
                this.ReplaceDBChars(dataModel.CrawlTimestamp),
                0));

            DatabaseConnect.Insert(builder.ToString());
        }

        public void UpdateStatement(DataModel dataModel, string infoIdValue, bool removedValue)
        {
            var builder = new StringBuilder();

            builder.Append(string.Format("UPDATE {0} ", this.tableName));
            builder.Append(
                string.Format(
                "SET {0}='{1}', {2}='{3}', {4}='{5}', {6}='{7}', {8}='{9}', {10}='{11}', {12}='{13}', {14}='{15}', {16}='{17}', {18}={19}, {20}={21} ",
                SourceName,
                this.ReplaceDBChars(dataModel.SourceName),
                SourceURL,
                this.ReplaceDBChars(dataModel.SourceUrl),
                Title,
                this.ReplaceDBChars(dataModel.Title),
                Author,
                this.ReplaceDBChars(dataModel.Author),
                SummaryDescription,
                this.ReplaceDBChars(dataModel.SummaryDescription),
                FullDescription,
                this.ReplaceDBChars(dataModel.FullDescription),
                OriginalPublishDate,
                this.ReplaceDBChars(dataModel.OriginalPublishDate),
                UpdatePublishDate,
                this.ReplaceDBChars(dataModel.UpdatePublishDate),
                Enclosure,
                this.ReplaceDBChars(dataModel.Enclosure),
                CrawlTimeStamp,
                this.ReplaceDBChars(dataModel.CrawlTimestamp),
                Removed,
                Convert.ToInt32(removedValue)));
            builder.Append(string.Format("WHERE {0}='{1};'", this.infoID, infoIdValue));

            DatabaseConnect.Update(builder.ToString());
        }

        public string SelectIndex(string url)
        {
            var builder = new StringBuilder();

            builder.Append(string.Format("SELECT {0} ", this.infoID));
            builder.Append(string.Format("FROM {0} ", this.tableName));
            builder.Append(string.Format("WHERE {0}='{1}';", SourceURL, url));

            return DatabaseConnect.Select(builder.ToString());
        }

        public DataModel SelectIndexAndDescriptions(string url)
        {
            var datamodel = new DataModel();
            var builder = new StringBuilder();

            builder.Append(string.Format("SELECT {0}, {1}, {2} ", this.infoID, SummaryDescription, FullDescription));
            builder.Append(string.Format("FROM {0} ", this.tableName));
            builder.Append(string.Format("WHERE {0}='{1}';", SourceURL, url));

            var rowCount = DatabaseConnect.SelectList(builder.ToString());
            for (int i = 0; i < rowCount.Count; i++)
            {
                int j = 0;
                datamodel = new DataModel()
                {
                    InfoID = rowCount[i][j++].ToString(),
                    SummaryDescription = rowCount[i][j++].ToString(),
                    FullDescription = rowCount[i][j++].ToString()
                };
            }

            return datamodel;
        }

        public string LastDBID()
        {
            var builder = new StringBuilder();
            ////SELECT LAST(column_name) FROM table_name;ORDER BY DESC
            builder.Append(
                string.Format(
                "SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10} ",
                SourceName,
                SourceURL,
                Title,
                Author,
                SummaryDescription,
                FullDescription,
                OriginalPublishDate,
                UpdatePublishDate,
                Enclosure,
                CrawlTimeStamp,
                Removed));
            builder.Append(string.Format("FROM {0}; ", this.tableName));
            ////builder.Append(string.Format("WHERE 1"));

            return DatabaseConnect.Select(builder.ToString());
        }

        public List<DataModel> AddToModel(List<DataModel> dataModelList)
        {
            var builder = new StringBuilder();
            ////SELECT LAST(column_name) FROM table_name;ORDER BY DESC
            builder.Append(
                string.Format(
                "SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10} ",
                this.infoID,
                SourceName,
                SourceURL,
                Title,
                Author,
                SummaryDescription,
                FullDescription,
                OriginalPublishDate,
                UpdatePublishDate,
                Enclosure,
                CrawlTimeStamp));
            builder.Append(string.Format("FROM {0}; ", this.tableName));

            dataModelList = this.SetModels(dataModelList, builder);

            return dataModelList;
        }

        private List<DataModel> SetModels(List<DataModel> dataModelList, StringBuilder builder)
        {
            var rowCount = DatabaseConnect.SelectList(builder.ToString());
            for (int i = 0; i < rowCount.Count; i++)
            {
                int j = 0;
                dataModelList.Add(new DataModel()
                {
                    InfoID = rowCount[i][j++].ToString(),
                    SourceName = rowCount[i][j++].ToString(),
                    SourceUrl = rowCount[i][j++].ToString(),
                    Title = rowCount[i][j++].ToString(),
                    Author = rowCount[i][j++].ToString(),
                    SummaryDescription = rowCount[i][j++].ToString(),
                    FullDescription = rowCount[i][j++].ToString(),
                    OriginalPublishDate = rowCount[i][j++].ToString(),
                    UpdatePublishDate = rowCount[i][j++].ToString(),
                    Enclosure = rowCount[i][j++].ToString(),
                    CrawlTimestamp = rowCount[i][j].ToString()
                });
            }

            return dataModelList;
        }

        private string ReplaceDBChars(string value)
        {
            return value.Replace("'", "''");
        }
    }
}